from SystematicFactors.General import *
import xlrd
import os

# 基金新成立个数
# 手动更新
def Load_Fund_NewSetup(database, pathFilename):
    fund_new = pd.read_csv('inputdata/fund_new.csv', encoding='gbk', header=0)
    date_trans = list(fund_new['date'])
    for i in range(len(date_trans)):
        date_trans[i] = datetime.datetime.strptime(date_trans[i],'%Y-%m-%d')
    fund_new['trade_date'] = date_trans
    fund_new.set_index(['trade_date'], inplace=True)
    print(fund_new)

    SaveBigFactorToDatabase(fund_new['股混基金新成立个数'], 'MF_New', "market_factor")
    SaveBigFactorToDatabase(fund_new['股混基金新成立金额'], 'MF_NewAmt', "market_factor")
    SaveBigFactorToDatabase(fund_new['私募股混新成立只数'], 'HF_New', "market_factor")
    SaveBigFactorToDatabase(fund_new['私募股混平均新成立金额'], 'HF_NewAmt', "market_factor")


# 公募仓位
# 基金-专题统计-资产配置-仓位估算-开放式基金估算股票投资比例-明细数据
def Load_MF_HoldingLevel(database, pathFilename, datetime1=None, datetime2=None):
    # 原版文件已经无从确认结构
    # stock_position = pd.read_csv('inputdata/stock_position.csv', encoding='gbk', header=0)
    # date_trans = list(stock_position['Date'])
    # for i in range(len(date_trans)):
    #     date_trans[i] = datetime.datetime.strptime(date_trans[i], '%Y-%m-%d')
    # stock_position['Date'] = date_trans
    # stock_position.set_index(['Date'], inplace=True)
    # SaveBigFactorToDatabase(stock_position['stock_position'], 'MF_HoldingLevel_Mix', "market_factor")
    # SaveBigFactorToDatabase(stock_position['stock_position2'], 'MF_HoldingLevel_Equity', "market_factor")
    # print(stock_position)

    df = pd.read_excel(pathFilename, encoding='gbk', header=0)
    df.dropna(subset=['股票投资比例(%)'], inplace=True)
    df.rename(columns={'股票投资比例(%)':'Position_Level'}, inplace=True)

    df["date_dt"] = pd.to_datetime(df["日期"])
    df["date"] = df["date_dt"].dt.date
    df.sort_values("日期", inplace=True)

    fill_historical_probability_percentile(df, field_name="Position_Level")
    fill_historical_absolute_value_percentile(df, field_name="Position_Level")

    # print(df.dtypes)
    # print(df[["date", 'Position_Level', "Position_Level_Percentile", "Position_Level_Rank"]])
    # print(df[["date", 'Position_Level', "Position_Level_Percentile", "Position_Level_Rank"]].tail())

    # 换频率
    df.set_index("date_dt", inplace=True)
    df_weekly = df.resample("W").last()
    df_weekly["Report_Date"] = df_weekly.index
    df_weekly["Release_Date"] = df_weekly["date"]
    df_weekly["Dif"] = df_weekly["Position_Level_Rank"] - df_weekly["Position_Level_Rank"].shift(1)
    # df_weekly.dropna(subset=["Position_Level", "Percentile"], inplace=True)
    # print(df_weekly[["Report_Date", "Release_Date", "Position_Level", "Position_Level_Percentile", "Position_Level_Rank"]])
    # print(df_weekly[["Report_Date", "Position_Level", "Position_Level_Rank", "Dif"]].tail())

    if datetime1 != None:
        df_weekly = df_weekly[df_weekly["Release_Date"] >= datetime1]
    #
    if datetime2 != None:
        df_weekly = df_weekly[df_weekly["Release_Date"] <= datetime2]

    # 储存
    Save_Systematic_Factor_To_Database(database, df_weekly, save_name='MF_Holdings_Level_Equity',
                                       field_name="Position_Level")
    Save_Systematic_Factor_To_Database(database, df_weekly, save_name='MF_Holdings_Level_Equity_Percentile',
                                       field_name="Position_Level_Percentile")
    Save_Systematic_Factor_To_Database(database, df_weekly, save_name='MF_Holdings_Level_Equity_Rank',
                                       field_name="Position_Level_Rank")
    Save_Systematic_Factor_To_Database(database, df_weekly, save_name='MF_Holdings_Level_Equity_Rank_Weekly_Dif',
                                       field_name="Dif")


def Process_Header(header_content):
    #
    headerIndexByName = {}
    headerCount = 0
    for header in header_content:
        header = header.strip(" ")
        header = header.strip("\n")
        # 处理重复表头
        if header in headerIndexByName.keys():
            temp = header.split("_")
            if len(temp) > 1:
                n = float(temp[1]) + 1
                header = header + "_" + n
            else:
                header = header + "_1"
        headerIndexByName[header] = headerCount
        headerCount = headerCount + 1
    #
    return headerIndexByName


# 读取优先股资料
# 股票-专题统计-一级市场-增发预配股-优先股基本资料
def Load_Preferred_Stock(database, pathFilename):
    #
    wb = xlrd.open_workbook(filename=pathFilename)
    sheet = wb.sheet_by_index(0)  # 通过索引获取表格
    #
    nrows = sheet.nrows
    ncols = sheet.ncols
    documents = []
    #
    header_content = sheet.row_values(0)
    headerIndexByName = Process_Header(header_content)
    #
    for i in range(1, nrows):
        row_data = sheet.row_values(i)
        content = row_data
        print(content)
        #
        if content[0] == "":
            break
        #
        document = {}
        document["Symbol"] = row_data[headerIndexByName["证券代码"]]
        document["Name"] = row_data[headerIndexByName["证券简称"]]
        document["Issuer"] = row_data[headerIndexByName["发行人"]]

        document["Plan_Announce_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["预案公告日"]]).date()
        document["Listing_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["上市日"]]).date()
        document["IEC_Announce_Date"] = Gadget.ParseExcelDatetime(row_data[headerIndexByName["发审委审核日"]]).date()

        document["Interest_Rate_Type"] = row_data[headerIndexByName["股息率类型"]]
        document["Coupon_Rate"] = row_data[headerIndexByName["票面股息率"]]

        document["Callable"] = row_data[headerIndexByName["是否可赎回"]]
        document["Convertible"] = row_data[headerIndexByName["是否可转股"]]
        document["Initial_Conversion_Price"] = row_data[headerIndexByName["最初转让价格"]]
        #
        document["Issue_Amount"] = row_data[headerIndexByName["募集资金总额(万元)"]] * 0.0001
        document["Issue_Amount_Net"] = row_data[headerIndexByName["募集资金净额(万元)"]] * 0.0001
        #
        document["CSRC_Industry"] = row_data[headerIndexByName["所属证监会行业"]]
        document["Wind_Industry"] = row_data[headerIndexByName["所属Wind行业"]]
        #
        document["Date"] = document["Plan_Announce_Date"]
        document["DateTime"] = document["Plan_Announce_Date"]
        document["Key2"] = document["Symbol"] + "_" \
                           + Gadget.ToDateString(document["Plan_Announce_Date"])

        for k , v in document.items():
            if v == "":
                document[k] = None
        #
        # print(document["Symbol"], document["Announce_Date"])
        # database.Upsert("stock", "IPO", [], document)
        # a = 0
        documents.append(document)
        if len(documents) > 100:
            database.Upsert_Many("stock", "preferred_stock", [], documents)
            documents.clear()
        #
    database.Upsert_Many("stock", "preferred_stock", [], documents)
    a = 0


if __name__ == '__main__':
    #
    pathfilename = os.getcwd() + "\..\Config\config2.json"
    config = Config.Config(pathfilename)
    database = config.DataBase("JDMySQL")
    realtime = config.RealTime()

    # Load_Fund_NewSetup(database, pathFilename='inputdata/fund_new.csv')
    Load_MF_HoldingLevel(database, pathFilename='C:/Users/fengshimeng3/Documents/财富管理-智能投顾/inputdata/开放式基金估算股票投资比例.xlsx')
    # Load_Preferred_Stock(database, pathFilename="C:/Users/fengshimeng3/Documents/财富管理-智能投顾/优先股基本资料.xlsx")

